﻿using Com.JunCaiSoft.Common.Dao;
using Com.JunCaiSoft.ConstructionCashFlow.Common.Enum;
using Com.JunCaiSoft.ConstructionCashFlow.Entity;
using Com.JunCaiSoft.ConstructionCashFlow.IDao;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Com.JunCaiSoft.ConstructionCashFlow.Dao
{
    public class ProjectSettlementDao : CoreDALAsync<ProjectSettlement>, IProjectSettlementDao
    {
        public ProjectSettlementDao(DbContext context) : base(context)
        {

        }

        public DbContext dbContext { get { return baseContext; } }
        /// <summary>
        /// 项目预结算
        /// </summary>
        /// <param name="BeginDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="ProcessingType"></param>
        /// <param name="Period"></param>
        /// <returns></returns>
        public async Task<bool> SettlementProjectAsync(DateTime BeginDate, DateTime EndDate,Int32 ProcessingType, SettlementPeriod Period)
        {
            try
            {
				var sql = @"INSERT INTO ProjectSettlement 
                            SELECT
								NEWID() Id,
								1 Num,
								1607257771 NumCode,
								a.Id SettlementProjectId,
								CAST ( @BeginTime AS datetime2 ( 7 ) ) BeginTime,
								CAST ( @EndTime AS datetime2 ( 7 ) ) EndTime,
								@Period SettlementPeriod,
								ISNULL(a.BeginBalance,0.0) BeginBalance,
								ISNULL(a.BeginBalance + a.Income - a.Cost,0.0) EndBalance,
								ISNULL(a.BeginFreezeMoney,0.0) BeginFreezeMoney,
								ISNULL(a.EndFreezeMoney,0.0) EndFreezeMoney,
								ISNULL(a.BeginBalance - a.BeginFreezeMoney,0.0) BeginFlowBalance,
								ISNULL(a.BeginBalance + a.Income - a.Cost - a.EndFreezeMoney,0.0) EndFlowBalance,
								ISNULL(a.BeginCashBalance,0.0) BeginCashBalance,
								ISNULL(a.BeginCashBalance + a.IncomeCash - a.CostCash,0.0) EndCashBalance,
								ISNULL(a.BeginCommercialTicket,0.0) BeginCommercialTicket,
								ISNULL(a.BeginCommercialTicket + a.IncomeCommercialTicket - a.CostCommercialTicket,0.0) EndCommercialTicket,
								ISNULL(a.BeginGongDiFang,0.0) BeginCommercialTicket,
								ISNULL(a.EndGongDiFang,0.0) EndGongDiFang,
								0 IsSettlement,
								GETDATE() CreateTime 
							FROM
								(
									SELECT
										a.Id,
										ROUND( AVG ( b.EndBalance ), 2 ) BeginBalance,
										ROUND( AVG ( b.EndCommercialTicket ), 2 ) BeginCommercialTicket,
										ROUND( AVG ( b.EndCashBalance ), 2 ) BeginCashBalance,
										ROUND( SUM ( CASE WHEN c.BankForward = 0 AND c.FundsType = 16 THEN c.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
										ROUND( SUM ( CASE WHEN c.BankForward = 0 AND c.FundsType != 16 THEN c.Amount ELSE 0.0 END ), 2 ) IncomeCash,
										ROUND( SUM ( CASE WHEN c.BankForward = 0 THEN c.Amount ELSE 0.0 END ), 2 ) Income,
										ROUND( SUM ( CASE WHEN c.BankForward = 1 AND c.FundsType = 16 THEN c.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
										ROUND( SUM ( CASE WHEN c.BankForward = 1 AND c.FundsType != 16 THEN c.Amount ELSE 0.0 END ), 2 ) CostCash,
										ROUND( SUM ( CASE WHEN c.BankForward = 1 THEN c.Amount ELSE 0.0 END ), 2 ) Cost,
										ROUND( AVG ( b.BeginFreezeMoney ), 2 ) BeginFreezeMoney,
										ROUND( AVG ( b.EndFreezeMoney ), 2 ) EndFreezeMoney,
										ROUND( AVG ( b.BeginGongDiFang ), 2 ) BeginGongDiFang,
										ROUND( AVG ( b.EndGongDiFang ), 2 ) EndGongDiFang
									FROM
										Project a
										LEFT JOIN (
											SELECT
												a.SettlementProjectId,
												ISNULL( ROUND( ISNULL(a.BeginBalance,0.0) + ISNULL(Income,0.0) - ISNULL(Cost,0.0), 2 ),0.0 )EndBalance ,
												ISNULL( ROUND( ISNULL(a.BeginCommercialTicket,0.0) + ISNULL(IncomeCommercialTicket,0.0) - ISNULL(CostCommercialTicket,0.0),2 ),0.0) EndCommercialTicket,
												ISNULL( ROUND( ISNULL(a.BeginCashBalance,0.0) + ISNULL(IncomeCash,0.0) - ISNULL(CostCash,0.0),2 ),0.0) EndCashBalance,
												a.BeginFreezeMoney,
												a.EndFreezeMoney,
												a.BeginGongDiFang,
												a.EndGongDiFang
											FROM
												ProjectSettlement a
												LEFT JOIN (
												SELECT
													a.ProjectId,
													ROUND( SUM ( CASE WHEN a.BankForward = 0 AND a.FundsType = 16 THEN a.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
													ROUND( SUM ( CASE WHEN a.BankForward = 0 AND a.FundsType != 16 THEN a.Amount ELSE 0.0 END ), 2 ) IncomeCash,
													ROUND( SUM ( CASE WHEN a.BankForward = 0 THEN a.Amount ELSE 0.0 END ), 2 ) Income,
													ROUND( SUM ( CASE WHEN a.BankForward = 1 AND a.FundsType = 16 THEN a.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
													ROUND( SUM ( CASE WHEN a.BankForward = 1 AND a.FundsType != 16 THEN a.Amount ELSE 0.0 END ), 2 ) CostCash,
													ROUND( SUM ( CASE WHEN a.BankForward = 1 THEN a.Amount ELSE 0.0 END ), 2 ) Cost 
												FROM
													CashFlow a 
												WHERE
													a.AllocationTime < @BeginTime 
												GROUP BY
													a.ProjectId 
											) b ON a.SettlementProjectId = b.ProjectId
											WHERE
												a.SettlementPeriod = 0 
										) b ON a.Id = b.SettlementProjectId
									LEFT JOIN ( SELECT * FROM CashFlow WHERE AllocationTime >= @BeginTime AND AllocationTime <= @EndTime ) c ON a.Id = c.ProjectId 
									GROUP BY
										a.Id 
								) a";

				List<ProjectSettlement> projectSettlementList = await ListAsync(x => x.BeginTime == BeginDate && x.EndTime == EndDate && x.SettlementPeriod == Period);



				if (projectSettlementList.Count != 0 && ProcessingType == 1)
				{
					await DeleteListAsync(projectSettlementList);
				}

				if (projectSettlementList.Count == 0 || ProcessingType == 1)
                {
					List<SqlParameter> settlementParams = new List<SqlParameter>();

					SqlParameter BeginTime = new SqlParameter("@BeginTime", BeginDate.ToString("yyyyMMdd"));

					SqlParameter EndTime = new SqlParameter("@EndTime", EndDate.ToString("yyyyMMdd"));

					SqlParameter computePeriod = new SqlParameter("@Period", Period);

					settlementParams.Add(BeginTime); settlementParams.Add(EndTime); settlementParams.Add(computePeriod);

					dbContext.Database.ExecuteSqlRaw(sql, settlementParams.Select(x => ((ICloneable)x).Clone()).ToArray());
				}

				return true;
            }
            catch(Exception e)
            {
                throw e;
            }
        }
    }
}
